insert overwrite table jms_dws.dws_actual_aging_sign_dt
select
billcode                         as billcode,
min(wid_columns)[7]              as scansitecode,
min(wid_columns)[1]              as scantime,
min(wid_columns)[2]              as inputtime,
min(wid_columns)[3]              as weight,
min(wid_columns)[4]              as send_deliver_user,
min(wid_columns)[5]              as send_deliver_usercode,
min(wid_columns)[6]              as scantype,
'prior_actual_sign'              as tabletype,
to_date(min(wid_columns)[1])     as dt
from (
    select
          billcode,
          split(concat('1','\001',
            coalesce(scantime_early,''),'\001',
            coalesce(inputtime_early,''),'\001',
            coalesce(weight_early,''),'\001',
            coalesce(send_deliver_user_early,''),'\001',
            coalesce(send_deliver_usercode_early,''),'\001',
            '出库扫描','\001',
            coalesce(scansitecode_early,'')
            ),'\001') as wid_columns
      from
          jms_dwd.dwd_barscan_collect_dt
          where  dt BETWEEN date_add('{{ execution_date | cst_ds }}', -24) AND '{{ execution_date | cst_ds }}'
                 and dimtype='billcode' and scantype_early = '出库扫描'
                        union all
    select
          billcode,
          split(concat('2','\001',
            coalesce(delivertime_early,''),'\001',
            coalesce(inputtime_early,''),'\001',
            coalesce(realweight_early,''),'\001',
            coalesce(deliveruser_early,''),'\001',
            coalesce(send_deliver_usercode_early,''),'\001',
            '签收扫描','\001',
            coalesce(scansitecode_early,'')
            ),'\001') as wid_columns
      from
          jms_dwd.dwd_barscan_sign_dt
          where  dt BETWEEN date_add('{{ execution_date | cst_ds }}', -24) AND '{{ execution_date | cst_ds }}'
                  and dimtype='billcode'
) tab
group by billcode
having
    to_date(min(wid_columns)[1]) between date_add('{{ execution_date | cst_ds }}', -14) AND '{{ execution_date | cst_ds }}'
distribute by dt , pmod(hash(rand()),12);
